package com.library.dao;

import com.library.entity.Student;
import com.library.util.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    private final QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());

    // 查询学生信息
    public Student getBySno(String sno) {
        String sql = "SELECT * FROM student WHERE sno = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(Student.class), sno);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 学生借书排行榜
    public List<Object[]> getBorrowRank() {
        String sql = "SELECT s.sno, s.username, COUNT(b.serNum) AS borrow_count " +
                "FROM student s JOIN borrowrec b ON s.sno = b.sno " +
                "GROUP BY s.sno, s.username ORDER BY borrow_count DESC LIMIT 5";
        try {
            List<Object[]> result = new ArrayList<>();
            List<Object[]> raw = queryRunner.query(sql, new ArrayListHandler());
            if (raw != null) result.addAll(raw);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    // 注册新学生
    public void insert(Student student) {
        String sql = "INSERT INTO student(sno, username, type, collage, major, birth, originPlace, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            queryRunner.update(sql, student.getSno(), student.getUsername(), student.getType(), student.getCollage(), student.getMajor(), student.getBirth(), student.getOriginPlace(), student.getPassword());
        } catch (SQLException e) {
            throw new RuntimeException("注册失败：" + e.getMessage(), e);
        }
    }

    // 根据学号查姓名
    public String getNameBySno(String sno) {
        String sql = "select username from student where sno = ?";
        try {
            return queryRunner.query(sql, new ScalarHandler<String>(), sno);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
